package com.infore.dataStatistics.main.utils;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC连接工具类
 */
@Data
@Slf4j
public class JdbcUtils {

    /**
     * JDBC驱动
     */
    private String driverClassName;
    /**
     * JDBC连接地址
     */
    private String url;
    /**
     * 用户名
     */
    private String username;
    /**
     * 密码
     */
    private String password;

    public JdbcUtils(String driverClassName, String url, String username, String password) {
        this.driverClassName = driverClassName;
        this.url = url;
        this.username = username;
        this.password = password;

        try {
            Class.forName(driverClassName);
        } catch (Exception e) {
            log.error("驱动加载失败:" + driverClassName);
        }

    }

    /**
     * 数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            log.error("数据库连接失败:" + url);
            log.error("/r/n");
            log.error("账号密码:" + username + "/" + password);
        }
        return conn;
    }

    /**
     * 关闭资源
     *
     * @param rs
     * @param state
     * @param conn
     */
    public void closeAll(ResultSet rs, Statement state, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("数据库连接关闭失败:" + conn);
            }
        }
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                log.error("数据库连接关闭失败:" + conn);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("数据库连接关闭失败:" + conn);
            }
        }
    }

    /**
     * 执行查询语句
     *
     * @param sql 查询语句
     * @return 结果集
     */
    public List<Map<String, Object>> executeQuery(String sql) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection conn = null;
        List<Map<String, Object>> res = new ArrayList<>();

        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();

            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(md.getColumnName(i), rs.getObject(i));
                }
                res.add(map);
            }
        } catch (SQLException e) {
            log.error("数据查询失败:" + sql);
        } finally {
            closeAll(rs, ps, conn);
        }

        return res;
    }

    /**
     * 执行增删改sql
     * @param sql 增删改语句
     * @param objects
     * @return 受影响行数
     */
    public int executeUpdateSDU(String sql, Object... objects) {
        //返回受影响行
        int rows = -1;
        // 1.加载驱动
        // 2.得到数据库连接：Connection指向的接口
        Connection conn = null;
        // 3.得到Statement：接口指向的对象，使用Statement对象发送sql语句到数据库中执行，返回结果集
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (objects != null) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject((i + 1), objects[i]);
                }
            }
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("数据增删改失败:" + sql);
        } finally {
            closeAll(null, ps, conn);
        }
        return rows;
    }

    public static void main(String[] args) {
        JdbcUtils jdbcUtils = new JdbcUtils("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3306/test1", "root", "shaolay@0825");
        List<Map<String, Object>> maps = jdbcUtils.executeQuery("SELECT * FROM t_hour_data");
        maps.stream().forEach(System.out::println);
    }

}
